﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace IisHost.UI
{
    public partial class DatabaseControl : UserControl
    {
        private const string CONNECTION_STR_FMT = "Data Source={0};Initial Catalog={1};User ID={2};Password={3};";

        public void BindData()
        {
            lstMessage.Visible = false;
        }

        protected void OnCommand(object sender, CommandEventArgs e)
        {
            lstMessage.Visible = false;
            lblMessage.Text = String.Empty;

            switch(e.CommandName)
            {
                case "RunScript":
                    {
                        if(fuScriptFile.HasFile)
                        {
                            string source = txtSource.Text.Trim();
                            string database = txtDatabase.Text.Trim();
                            string username = txtUsername.Text.Trim();
                            string password = txtPassword.Text.Trim();
                            var cmds = new List<String>();

                            try
                            {
                                if(cbCreateDatabase.Checked)
                                {
                                    using(var connection = new SqlConnection(String.Format(CONNECTION_STR_FMT,
                                        source,
                                        "master",
                                        username,
                                        password)))
                                    {
                                        connection.Open();
                                        using(var cmd = connection.CreateCommand())
                                        {
                                            cmd.CommandText = String.Format("IF NOT EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'{0}') CREATE DATABASE [{0}] COLLATE SQL_Latin1_General_CP1_CI_AS", 
                                                database);
                                            cmd.ExecuteNonQuery();
                                        }
                                    }
                                }

                                using(var reader = new StreamReader(fuScriptFile.PostedFile.InputStream))
                                {
                                    string line = null;
                                    var cmdBuilder = new StringBuilder();

                                    while((line = reader.ReadLine()) != null)
                                    {
                                        if(line.Trim().ToUpperInvariant().Equals("GO"))
                                        {
                                            cmds.Add(cmdBuilder.ToString());
                                            cmdBuilder.Length = 0;
                                        }
                                        else
                                        {
                                            cmdBuilder.AppendLine(line);
                                        }
                                    }
                                }

                                using(var connection = new SqlConnection(String.Format(CONNECTION_STR_FMT,
                                    source, 
                                    database, 
                                    username, 
                                    password)))
                                {
                                    connection.Open();
                                    foreach(var i in cmds)
                                    {
                                        using(var cmd = connection.CreateCommand())
                                        {
                                            try
                                            {
                                                cmd.CommandText = i;
                                                cmd.ExecuteNonQuery();
                                            }
                                            catch(Exception ex)
                                            {
                                                lblMessage.Text += String.Format("<li style=\"color: red;\">{0}</li>", ex.Message);
                                            }
                                        }
                                    }
                                }
                                lblMessage.Text += String.Format("<li style=\"color: green;\">Operation completed successfully.</li>");
                            }
                            catch(Exception ex)
                            {
                                lblMessage.Text += String.Format("<li style=\"color: red;\">{0}</li>", ex.Message);
                            }
                            lstMessage.Visible = true;
                        }
                    }
                    break;
            }
        }
    }
}